#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
银行管理系统报表生成器
支持多种报表类型：交易统计、月末汇总、客户分析等
"""

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif'] = ['SimHei']  # 设置中文字体
matplotlib.rcParams['axes.unicode_minus'] = False
import io
import base64
from datetime import datetime, timedelta
from app import app, db
from models.models import User, BankCard, Transaction, UserAuth, DepositBusiness
from sqlalchemy import func, text
import os

class ReportGenerator:
    def __init__(self):
        self.reports_dir = "reports"
        if not os.path.exists(self.reports_dir):
            os.makedirs(self.reports_dir)
    
    def generate_transaction_summary(self, start_date=None, end_date=None):
        """生成交易汇总报表"""
        with app.app_context():
            # 构建查询条件
            query = db.session.query(
                Transaction.type,
                func.count(Transaction.id).label('count'),
                func.sum(Transaction.amount).label('total_amount')
            ).group_by(Transaction.type)
            
            if start_date:
                query = query.filter(Transaction.timestamp >= start_date)
            if end_date:
                query = query.filter(Transaction.timestamp <= end_date + ' 23:59:59')
            
            results = query.all()
            
            # 转换为DataFrame
            data = []
            for result in results:
                type_name = {
                    'deposit': '存款',
                    'withdraw': '取款',
                    'transfer': '转账'
                }.get(result.type, result.type)
                
                data.append({
                    '交易类型': type_name,
                    '交易笔数': result.count,
                    '交易金额(元)': round(result.total_amount, 2)
                })
            
            df = pd.DataFrame(data)
            return df
    
    def generate_monthly_summary(self, year=None, month=None):
        """生成月末汇总报表"""
        if not year:
            year = datetime.now().year
        if not month:
            month = datetime.now().month
        
        with app.app_context():
            # 获取指定月份的开始和结束日期
            start_date = datetime(year, month, 1)
            if month == 12:
                end_date = datetime(year + 1, 1, 1) - timedelta(days=1)
            else:
                end_date = datetime(year, month + 1, 1) - timedelta(days=1)
            
            # 查询月度统计数据
            monthly_stats = db.session.query(
                func.count(Transaction.id).label('total_transactions'),
                func.sum(Transaction.amount).label('total_amount'),
                func.avg(Transaction.amount).label('avg_amount')
            ).filter(
                Transaction.timestamp >= start_date,
                Transaction.timestamp <= end_date
            ).first()
            
            # 查询各类型交易统计
            type_stats = db.session.query(
                Transaction.type,
                func.count(Transaction.id).label('count'),
                func.sum(Transaction.amount).label('amount')
            ).filter(
                Transaction.timestamp >= start_date,
                Transaction.timestamp <= end_date
            ).group_by(Transaction.type).all()
            
            # 查询活跃银行卡
            active_cards = db.session.query(
                Transaction.card_id,
                func.count(Transaction.id).label('transaction_count'),
                func.sum(Transaction.amount).label('total_amount')
            ).filter(
                Transaction.timestamp >= start_date,
                Transaction.timestamp <= end_date
            ).group_by(Transaction.card_id).order_by(
                func.sum(Transaction.amount).desc()
            ).limit(10).all()
            
            # 构建报表数据
            summary_data = {
                '统计项目': [
                    '总交易笔数',
                    '总交易金额(元)',
                    '平均交易金额(元)',
                    '统计月份'
                ],
                '数值': [
                    monthly_stats.total_transactions or 0,
                    round(monthly_stats.total_amount or 0, 2),
                    round(monthly_stats.avg_amount or 0, 2),
                    f"{year}年{month}月"
                ]
            }
            
            type_data = []
            for stat in type_stats:
                type_name = {
                    'deposit': '存款',
                    'withdraw': '取款',
                    'transfer': '转账'
                }.get(stat.type, stat.type)
                
                type_data.append({
                    '交易类型': type_name,
                    '交易笔数': stat.count,
                    '交易金额(元)': round(stat.amount, 2)
                })
            
            card_data = []
            for card in active_cards:
                bank_card = BankCard.query.get(card.card_id)
                card_data.append({
                    '银行卡号': bank_card.card_number if bank_card else '未知',
                    '交易笔数': card.transaction_count,
                    '交易金额(元)': round(card.total_amount, 2)
                })
            
            return {
                'summary': pd.DataFrame(summary_data),
                'type_stats': pd.DataFrame(type_data),
                'top_cards': pd.DataFrame(card_data),
                'period': f"{year}年{month}月"
            }
    
    def generate_customer_analysis(self):
        """生成客户分析报表"""
        with app.app_context():
            # 查询客户统计
            customer_stats = db.session.query(
                func.count(User.id).label('total_customers'),
                func.count(BankCard.id).label('total_cards'),
                func.avg(BankCard.balance).label('avg_balance')
            ).outerjoin(BankCard, User.id == BankCard.user_id).first()
            
            # 查询余额分布
            balance_ranges = [
                (0, 1000, '0-1000元'),
                (1000, 5000, '1000-5000元'),
                (5000, 10000, '5000-10000元'),
                (10000, 50000, '10000-50000元'),
                (50000, float('inf'), '50000元以上')
            ]
            
            balance_distribution = []
            for min_bal, max_bal, range_name in balance_ranges:
                if max_bal == float('inf'):
                    count = BankCard.query.filter(BankCard.balance >= min_bal).count()
                else:
                    count = BankCard.query.filter(
                        BankCard.balance >= min_bal,
                        BankCard.balance < max_bal
                    ).count()
                
                balance_distribution.append({
                    '余额范围': range_name,
                    '银行卡数量': count
                })
            
            # 查询活跃客户（有交易记录的客户）
            active_customers = db.session.query(
                User.name,
                func.count(Transaction.id).label('transaction_count'),
                func.sum(Transaction.amount).label('total_amount')
            ).join(BankCard, User.id == BankCard.user_id).join(
                Transaction, BankCard.id == Transaction.card_id
            ).group_by(User.id, User.name).order_by(
                func.sum(Transaction.amount).desc()
            ).limit(10).all()
            
            active_data = []
            for customer in active_customers:
                active_data.append({
                    '客户姓名': customer.name,
                    '交易笔数': customer.transaction_count,
                    '交易金额(元)': round(customer.total_amount, 2)
                })
            
            # 修复DataFrame创建方式
            customer_stats_data = {
                '统计项目': ['总客户数', '总银行卡数', '平均余额(元)'],
                '数值': [
                    customer_stats.total_customers,
                    customer_stats.total_cards,
                    round(customer_stats.avg_balance or 0, 2)
                ]
            }
            
            return {
                'customer_stats': pd.DataFrame(customer_stats_data),
                'balance_distribution': pd.DataFrame(balance_distribution),
                'active_customers': pd.DataFrame(active_data)
            }
    
    def export_to_excel(self, data, filename):
        """导出数据到Excel文件"""
        filepath = os.path.join(self.reports_dir, filename)
        
        with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
            if isinstance(data, dict):
                for sheet_name, df in data.items():
                    df.to_excel(writer, sheet_name=sheet_name, index=False)
            else:
                data.to_excel(writer, index=False)
        
        return filepath
    
    def create_charts(self, data, chart_type='transaction_summary'):
        """创建图表"""
        try:
            plt.figure(figsize=(12, 8))
            
            if chart_type == 'transaction_summary':
                if isinstance(data, dict) and 'type_stats' in data:
                    df = data['type_stats']
                else:
                    df = data
                
                # 检查数据是否为空
                if df.empty:
                    plt.text(0.5, 0.5, '暂无数据', ha='center', va='center', transform=plt.gca().transAxes, fontsize=16)
                    plt.title('交易统计')
                else:
                    plt.subplot(2, 2, 1)
                    plt.pie(df['交易金额(元)'], labels=df['交易类型'], autopct='%1.1f%%')
                    plt.title('交易金额分布')
                    
                    plt.subplot(2, 2, 2)
                    plt.bar(df['交易类型'], df['交易笔数'])
                    plt.title('交易笔数统计')
                    plt.xticks(rotation=45)
                    
                    if 'top_cards' in data and not data['top_cards'].empty:
                        plt.subplot(2, 2, 3)
                        top_cards = data['top_cards'].head(5)
                        plt.barh(top_cards['银行卡号'], top_cards['交易金额(元)'])
                        plt.title('活跃银行卡TOP5')
                
            elif chart_type == 'customer_analysis':
                if 'balance_distribution' in data and not data['balance_distribution'].empty:
                    plt.subplot(2, 2, 1)
                    balance_df = data['balance_distribution']
                    plt.pie(balance_df['银行卡数量'], labels=balance_df['余额范围'], autopct='%1.1f%%')
                    plt.title('余额分布')
                
                if 'active_customers' in data and not data['active_customers'].empty:
                    plt.subplot(2, 2, 2)
                    active_df = data['active_customers'].head(5)
                    plt.barh(active_df['客户姓名'], active_df['交易金额(元)'])
                    plt.title('活跃客户TOP5')
                else:
                    plt.text(0.5, 0.5, '暂无客户数据', ha='center', va='center', transform=plt.gca().transAxes, fontsize=16)
                    plt.title('客户分析')
            
            plt.tight_layout()
            
            # 保存图表到内存
            img_buffer = io.BytesIO()
            plt.savefig(img_buffer, format='png', dpi=300, bbox_inches='tight')
            img_buffer.seek(0)
            img_data = base64.b64encode(img_buffer.getvalue()).decode()
            plt.close()
            
            return img_data
            
        except Exception as e:
            print(f"图表生成错误: {e}")
            # 返回一个简单的错误图表
            plt.figure(figsize=(8, 6))
            plt.text(0.5, 0.5, f'图表生成失败\n{str(e)}', ha='center', va='center', transform=plt.gca().transAxes, fontsize=12)
            plt.title('错误')
            
            img_buffer = io.BytesIO()
            plt.savefig(img_buffer, format='png', dpi=300, bbox_inches='tight')
            img_buffer.seek(0)
            img_data = base64.b64encode(img_buffer.getvalue()).decode()
            plt.close()
            
            return img_data

# 全局报表生成器实例
report_generator = ReportGenerator() 